package org.prs.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.prs.model.Photo;
import org.prs.persistence.connector.MySQLDatabaseConnector;

public class PhotoDatabasePersitenceManager {

    private Connection conn;
    private ResultSet result;

    public boolean insertPhoto(Photo photo) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertChildPhoto = null;
        try {
            insertChildPhoto = conn.prepareStatement("insert into photo set "
                    + " datecreated=STR_TO_DATE(?,'%d/%m/%Y'), name=?, path=?, childid=?, "
                    + "creatorid=?");

            insertChildPhoto.setString(1, photo.getDateCreated());
            insertChildPhoto.setString(2, photo.getName());
            insertChildPhoto.setString(3, photo.getPath());
            insertChildPhoto.setInt(4, photo.getChildId());
            insertChildPhoto.setInt(5, photo.getCreatorId());
            insertChildPhoto.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public Photo getPhotoById(int id) {
        Photo photo = new Photo();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectPhotoById = null;
        try {
            selectPhotoById = conn.prepareStatement("select * from photo where photoid=?");
            selectPhotoById.setInt(1, id);
            result = selectPhotoById.executeQuery();
            while (result.next()) {
                photo = getPhoto(result);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return photo;
    }

    public List<Photo> getChildPhotos(int childid) {
        List<Photo> photos = new ArrayList<Photo>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectPhotosByChildId = null;
        try {
            selectPhotosByChildId = conn.prepareStatement("select * from photo where childid=?");
            selectPhotosByChildId.setInt(1, childid);
            result = selectPhotosByChildId.executeQuery();
            while (result.next()) {
                photos.add(getPhoto(result));
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return photos;
    }

    public int getPhotoCount(int childId) {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectPhotoCount = null;
        try {
            selectPhotoCount = conn.prepareStatement("select count(*) from photo where childid=?");
            selectPhotoCount.setInt(1, childId);
            result = selectPhotoCount.executeQuery();
            while (result.next()) {

                return result.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return 0;
    }

    public int getNewPhotoId() {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectNewPhotoid = null;
        try {
            selectNewPhotoid = conn.prepareStatement("select max(photoid) from photo");
            result = selectNewPhotoid.executeQuery();
            while (result.next()) {

                return (result.getInt(1) + 1);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return 0 + 1;
    }

    public Photo getPhoto(ResultSet result) {
        Photo photo = new Photo();
        try {
            photo = new Photo();
            photo.setChildId(result.getInt("childid"));
            photo.setDateCreated(result.getString("datecreated"));
            photo.setName(result.getString("name"));
            photo.setPath(result.getString("path"));
            photo.setCreatorId(result.getInt("creatorid"));
            photo.setPhotoId(result.getInt("photoid"));
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return photo;
    }
}
